package agenceVoyageVirtuel.server.base;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import agenceVoyageVirtuel.client.beans.AdresseData;
import agenceVoyageVirtuel.client.beans.CarteData;
import agenceVoyageVirtuel.client.beans.ClientData;
import agenceVoyageVirtuel.client.beans.PersonneData;
import agenceVoyageVirtuel.server.DB_Conn;

public class DB_Client extends DB_Conn  {
	/**
	 * constructor - nothing to do
	 */
	public DB_Client() {
		// nothing to do
	}
	
	public ClientData isAuthentifie(String email, String password) {
		String query = "SELECT idClient, timeStamp, email, password, fixe, mobile, t_adresse.idAdresse, numero, rue, ville, codePostal, t_personne.idPersonne, prenom, nom"
				+ " FROM t_client JOIN t_adresse, t_personne"
				+ " WHERE email='" + email
				+ "' AND password='" + password
				+ "' AND t_client.idAdresse=t_adresse.idAdresse"
				+ " AND t_adresse.idPersonne=t_personne.idPersonne;";

		// prepare for rpc transport
		ClientData clientData= null;
		
		try {
			Connection connection = getConn();
			Statement select = connection.createStatement();
			ResultSet result = select.executeQuery(query);
			int i = 0;
			
			while (result.next()) {
				clientData = new ClientData();
				clientData.setIdClient(result.getInt(1));
				clientData.setDateInscription(result.getTimestamp(2).toString());
				clientData.setEmail(result.getString(3));
				clientData.setPassword(result.getString(4));
				clientData.setFixe(result.getString(5));
				clientData.setMobile(result.getString(6));
				//Adresse
				clientData.setAdresseClient(new AdresseData());
				clientData.getAdresseClient().setIdAdresse(result.getInt(7));
				clientData.getAdresseClient().setNumero(result.getInt(8));
				clientData.getAdresseClient().setRue(result.getString(9));
				clientData.getAdresseClient().setVille(result.getString(10));
				clientData.getAdresseClient().setCodePostal(result.getInt(11));
				//Personne
				clientData.getAdresseClient().setPersonne(new PersonneData());
				clientData.getAdresseClient().getPersonne().setIdPersonne(result.getInt(12));
				clientData.getAdresseClient().getPersonne().setPrenom(result.getString(13));
				clientData.getAdresseClient().getPersonne().setNom(result.getString(14));

				i++;
			}

			// clean up
			result.close();
			connection.close();
		} catch(Exception e) {
			System.err.println("Mysql Statement Error: " + query);
			e.printStackTrace();
		}

		// return datas
		return clientData;		
	}
	
	private int chercherClient(ClientData client) {
		String query = "SELECT idClient"
				+ " FROM t_client"
				+ " WHERE email=" + client.getEmail()
				+ " OR mobile=" + client.getMobile()
				+ ";";
	
		// prepare for rpc transport
		int id = -1;
		
		try {
			Connection connection = getConn();
			Statement select = connection.createStatement();
			ResultSet result = select.executeQuery(query);
			
			while(result.next()) {
				id = result.getInt(1); //idCarte
				break;
			}
	
			// clean up
			result.close();
			connection.close();
		} catch(Exception e) {
			System.err.println("Mysql Statement Error: " + query);
			e.printStackTrace();
		}
	
		// return datas
		return id;		
	}		
	
	public void creerClient(ClientData client) {
		client.setIdClient(chercherClient(client));
		
		if(client.getIdClient() != -1) {
			client.setIdClient(-1);
			return;
		}
		
		new DB_Adresse().creerAdresse(client.getAdresseClient());
		
		String query = "INSERT INTO t_client (idAdresse, email, password, fixe, mobile)"
				+ " VALUES (" + client.getAdresseClient().getIdAdresse()
				+ ", " + client.getEmail()
				+ ", " + client.getPassword()
				+ ", " + client.getFixe()
				+ ", " + client.getMobile()
				+ ");";
	
		try {
			Connection connection = getConn();
			PreparedStatement  prepareStatement = connection.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
			prepareStatement.executeUpdate();
	        ResultSet keys = prepareStatement.getGeneratedKeys();
	        
	        while(keys.next()) {
	        	client.setIdClient(keys.getInt(1)); //idClient
	        	break;
	        }
			
	        keys.close();
	        prepareStatement.close();
			connection.close();
		} catch(Exception e) {
			System.err.println("Mysql PrepareStatement Error: " + query);
			e.printStackTrace();
		}	
		
		return;
	}
}
